package com.lyh.dao;

import com.lyh.pojo.Book;
import com.lyh.pojo.BookVo;
import com.lyh.pojo.PageBean;
import com.lyh.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.taglibs.standard.extra.spath.Step;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能
 * 作者：lyh
 * 日期：2024/8/8 10:47
 */
public class BookDao {
    QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());

    public PageBean<BookVo> findAll(int pageNum, int pageSize) throws SQLException {
        PageBean<BookVo> pageBean = new PageBean<>();
        //当前页
        pageBean.setPageNum(pageNum);
        //每页记录数
        pageBean.setPageSize(pageSize);
        //1.得到总记录数
        String sql1 = "SELECT COUNT(*) FROM books";
        //单行单列使用ScalarHandler
        Number o = (Number)queryRunner.query(sql1,new ScalarHandler());
        //转化成int类型
        int total = o.intValue();
        pageBean.setTotal(total);

        //2.得到每页具体展示的数据
        String sql2 = "SELECT b.id,b.bname,b.author,b.num,b.page,b.price,c.cname\n" +
                "FROM books b INNER JOIN category AS c\n" +
                "ON b.cid = c.`cid` ORDER BY b.id LIMIT ?,?";
        List<BookVo> bookList = queryRunner.query(sql2,new BeanListHandler<>(BookVo.class),(pageNum-1)*pageSize,pageSize);
        //使用pageBean把list设置进去
        pageBean.setBeanList(bookList);
        return pageBean;
    }

    public void addBook(Book book) throws SQLException {
        String sql = "insert into books values(null,?,?,?,?,?,?)";
        queryRunner.update(sql,book.getBname(),book.getAuthor(),book.getNum(),book.getPage(),book.getPrice(),book.getCid());
    }

    public PageBean<BookVo> like(int pageNum, int pageSize, String likeName) throws SQLException {
        PageBean<BookVo> pageBean = new PageBean<>();
        pageBean.setPageNum(pageNum);
        pageBean.setPageSize(pageSize);
        //1.得到总记录数
        String sql1 = "SELECT COUNT(*) FROM books where bname like ?";
        //单行单列使用ScalarHandler
        Number o = (Number)queryRunner.query(sql1,new ScalarHandler(),"%"+likeName+"%");
        //转化成int类型
        int total = o.intValue();
        pageBean.setTotal(total);

        //2.得到每页具体展示的数据
        String sql2 = "SELECT b.id,b.bname,b.author,b.num,b.page,b.price,c.cname\n" +
                "FROM books b INNER JOIN category AS c\n" +
                "ON b.cid = c.`cid` and b.bname like ? ORDER BY b.id LIMIT ?,?";
        List<BookVo> list = queryRunner.query(sql2,new BeanListHandler<>(BookVo.class),"%"+likeName+"%",(pageNum-1)*pageSize,pageSize);
        //使用pageBean把list设置进去
        pageBean.setBeanList(list);
        return pageBean;
    }

    public void deleteById(int id) throws SQLException {
        String sql = "delete from books where id=?";
        queryRunner.update(sql,id);
    }

    public Book findById(int id) throws SQLException {
        String sql = "select * from books where id=?";
        return queryRunner.query(sql,new BeanHandler<>(Book.class),id);
    }

    public void updateBook(Book book) throws SQLException {
        String sql = "update books set bname=?,author=?,num=?,page=?,price=?,cid=? where id=?";
        queryRunner.update(sql,book.getBname(),book.getAuthor(),book.getNum(),book.getPage(),book.getPrice(),book.getCid(),book.getId());

    }
}
